<?php
/*! \brief
 * WidgetFramework Data Object (database wrapper)
 * Created: 200904 (TT Tsai)
 * Updated: 20090506 (TT Tsai) - Removed query(), and use prepare() and execute() to prevent sql injection
 *
 * This object covers all database details.
 * \author TT Tsai
 */
/* Methods
*/
?>
<?php
class WFDatabaseObject {
/**
     * @var PDO
     */
    protected $dbh; // DB handler, the PDO object
    protected $dbconf = array(); // DB configurations
    protected $errMessage = ""; // Error message
    /**
     * @var WF
     */
    protected $objFramework;
//private   $query_result; // The PDOStatement object

    /*
     * Constructor
     * Input: database configurations (an associative array)
     * Return: void
     * Description:
     *      - create PDO object depending on database type
     *      - after object is created, you have to check error. Use isFailed()
    */
    public function __construct( $dbconfig ,$objFramework = null) {
        if(null !== $objFramework){
            $this->objFramework = $objFramework;
        }
        $this->dbconf = $dbconfig;
        $dbConnString = "";
        $dbuser = "";
        $dbpass = "";
//$this->query_result = null;

        // db type checking
        if( !isset($dbconfig["type"]) ) {
            $this->errMessage = "The type of database was not specified.";
            return;
        }

        switch($dbconfig["type"]) {
        case "mysql":
            /* check parameters */
            if( (!isset($dbconfig["host"]) || $dbconfig["host"] == "") ||
                //(!isset($dbconfig["dbname"]) || $dbconfig["dbname"] == "") ||
                (!isset($dbconfig["username"]) || $dbconfig["username"] == "") ||
                (!isset($dbconfig["password"]) || $dbconfig["password"] == "")) {
                $this->errMessage = "Couldn't connect to MySQL database due to incorrect parameters.";
                return;
            }
            $dbConnString = "mysql:host=".$dbconfig["host"];
            // If MySQL execute create database SQL Statement will set $dbconfig["dbname"]='';
            if($dbconfig["dbname"]){
                $dbConnString .= ";dbname=".$dbconfig["dbname"];
            }
            $dbuser = $dbconfig["username"];
            $dbpass = $dbconfig["password"];
            //var_dump($dbConnString,$dbuser,$dbpass);
            break;
        case "sqlite":
            /* check parameters */
            if( (!isset($dbconfig["dbfile"]) || $dbconfig["dbfile"] == "") ) {
                $this->errMessage = "SQLite database file is empty.";
                return;
            }
            $dbConnString = "sqlite:".$dbconfig["dbfile"];
            break;
        default:
            $this->errMessage = "We don't support ".$dbconfig["type"]." database driver yet.";
            return;
        }
        // create PDO object (PHP Data Object)
        try {
            $this->dbh = new PDO( $dbConnString, $dbuser, $dbpass);
            // set attribute
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch( PDOException $exception ) {
            $this->errMessage = $exception->getMessage();
        }
        return;
    }

    /*
     * query() - main query function
     * Input:
     *  - $sqlString: the SQL string BEFORE valud assign. (e.g. 'select * from TABLE where filed=:value1')
     *  - $sqlValues: name value pairs. (e.g. array(':value1' => 'content1', ':value2' => 'content2'))
     * Return:
     *  - on success: query result (associative array)
     *  - on failure: FALSE
     * Description:
     *  - Using prepare() and execute() instead of query() to prevent sql injection
     * Note:
     *  - Instead of return value, we recommend that you can always use isFailed() to check successful or failed.
     *  - So if you are running 'INSERT' or 'UPDATE', you don't have to care return value, check isFailed().
     */
    public function query( $sqlString, $sqlValues=null) {
        try {
            // prepare() the SQL statement
            $queryobj = $this->dbh->prepare($sqlString); // return a PDOStatement -> $queryobj
            // execute SQL statement
            if( $queryobj->execute($sqlValues) == FALSE ) {
                $errInfo = $queryobj->errorInfo();
                $this->errMessage = $errInfo[2]; // $errInfo[2] is the error message
                myerror_log('DB query Error: $sqlString['.$sqlString.'] | $sqlValues:'.WF::getVarLog($sqlValues).' | Error Msg : ['.$this->errMessage.']');
                return FALSE;
            }
            if( stripos($sqlString, "SELECT") === 0 ) {
                $ret = $queryobj->fetchAll(PDO::FETCH_ASSOC);
                return $ret;
            } else {
                return TRUE;
            }
        } catch (PDOException $exception) {
            $this->errMessage = $exception->getMessage();
            myerror_log('DB query Error: $sqlString['.$sqlString.'] | $sqlValues:'.WF::getVarLog($sqlValues).' | Error Msg : ['.$this->errMessage.']');
            return FALSE;
        }
    }
    /**
     *\brief Description
     * Execute multiple sql statement
     * EX : DELETE FROM [WP_UPDATE_LOG];DELETE FROM sqlite_sequence WHERE name='WP_UPDATE_LOG';
     */
    public function queries($mixSqlString){
        $arrSql = array();
        if(is_array($mixSqlString) && $mixSqlString){
            $arrSql = $mixSqlString;
        }elseif(is_string($mixSqlString)){
            $arrSql = explode(';',$mixSqlString);
        }
        $isSuccess = true;
        foreach($arrSql as $strSql){
            if($strSql){
                $isSuccessTmp = $this->query($strSql);
                if($isSuccessTmp === false){ //if db query failed
                    $isSuccess = $isSuccess && $isSuccessTmp;
                    $strMsg = __CLASS__.'::'.__FUNCTION__.'()=>Database query failed! Err Msg :'.$this->getErrMessage();
                    myerror_log($strMsg);
                }
            }
        }
        return $isSuccess;
    }
    public function lastInsertId() {
        $lastid = 0;
        try {
            $lastid = $this->dbh->lastInsertId();
        }
        catch (PDOException $exception ) {
            $this->errMessage = $exception->getMessage();
        }
        return $lastid;
    }

    public function escape_string( $str ) {
        switch( $this->dbconf["type"] ){
        case "mysql":
            return mysql_escape_string($str);
        case "sqlite":
            return sqlite_escape_string($str);
        }
        return $str;
    }
    /**
     *\brief Description
     * Initiates a transaction 
     * Returns TRUE on success or FALSE on failure. 
     * @return bool
     */
    public function beginTransaction(){
        return $this->dbh->beginTransaction();
    }
    /**
     *\brief Description
     * Commits a transaction 
     * Returns TRUE on success or FALSE on failure.
     * @return bool
     */
    public function commit(){
        return $this->dbh->commit();
    }
    /**
     *\brief Description
     * Rolls back a transaction 
     * Returns TRUE on success or FALSE on failure.
     * @return bool
     */
    public function rollBack(){
        return $this->dbh->rollBack();
    }
    /**
     *\brief Description
     * Get the value of the first and first column.
     * @param string $sql SQL statement
     * @return value
     */
    public function fetchOne($sql,$sqlvalues = null){
        $result = $this->query($sql,$sqlvalues);
        $ret = ''; //default
        if(false === $result){
            $ret = false;
        }elseif(isset($result[0]) && is_array($result[0]) && $result[0]){
        	list($key,$value) = each($result[0]);
        	$ret = $value;
        }elseif(is_array($result) && $result){
        	list($key,$value) = each($result);
        	$ret = $value;
        }
        return $ret;
    }
    /**
     *\brief Description
     * Get the value of the first row data.
     * @param string $sql SQL statement
     * @return value
     */
    public function fetch($sql,$sqlvalues = null){
        $result = $this->query($sql,$sqlvalues);
        return isset($result[0]) ? $result[0] : array();
    }
    /*
     * Error handling
     */
    public function isFailed() {
        if( $this->errMessage != "" ) {
            return TRUE;
        }
        return FALSE;
    }
    public function getErrMessage() {
        return $this->errMessage;
    }
	/**
     *\brief Description
     * This method will get SELECT SQL statement And $assocDataPDO by $objParameter for Debug use
     * EX : SQL statement = SELECT widget_id FROM WIDGET WHERE widget_name=:widget_name 
     * 		$assocDataPDO = array(':widget_name'=>'modFlickr');
     */
    public function queryInfoByParameter($objParameter){
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        switch($objParameter->getQueryType()){
            case 'SelectAll':
                $sql = $objSql->getSqlSelectByParameter($objParameter);
            break;
            case 'SelectCount':
                $sql = $objSql->getSqlSelectCountByParameter($objParameter);
            break;
            case 'SelectRow':
                $sql = $objSql->getSqlSelectByParameter($objParameter);
            break;
            case 'SelectOne':
                $sql = $objSql->getSqlSelectByParameter($objParameter);
            break;
            case 'Insert':
                $sql = $objSql->getSqlInsertByParameter($objParameter);
            break;
            case 'Update':
                $sql = $objSql->getSqlUpdateByParameter($objParameter);
            break;
            case 'Delete':
                $sql = $objSql->getSqlDeleteByParameter($objParameter);
            break;
            
        }
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = array($sql,$sqlvalues);
        return $ret;
    }
    /**
     *\brief Description
     * This method will get SELECT COUNT SQL statement By $objParameter
     * EX : execute $sql = SELECT COUNT(widget_id) FROM WIDGET WHERE widget_name=:widget_name 
     * => return result fetchOne($sql)
     */
    public function querySelectCountByParameter($objParameter){
        $objParameter->setQueryType('SelectCount'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlSelectCountByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->fetchOne($sql,$sqlvalues);
        return $ret;
    }
    /**
     *\brief Description
     * This method will get SELECT SQL statement By $objParameter
     * EX : execute $sql = SELECT widget_id FROM WIDGET WHERE widget_name=:widget_name 
     * => return result query($sql)
     */
    public function querySelectAllByParameter($objParameter){
        $objParameter->setQueryType('SelectAll'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlSelectByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->query($sql,$sqlvalues);
        return $ret;
    }
	
    /**
     *\brief Description
     * This method will get SELECT SQL statement By $objParameter
     * EX : execute $sql = SELECT widget_id FROM WIDGET WHERE widget_name=:widget_name 
     * => return result fetch($sql)
     */
    public function querySelectRowByParameter($objParameter){
        $objParameter->setQueryType('SelectRow'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlSelectByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->fetch($sql,$sqlvalues);
        return $ret;
    }
	/**
     *\brief Description
     * This method will get SELECT SQL statement By $objParameter
     * EX : execute $sql = SELECT widget_id FROM WIDGET WHERE widget_name=:widget_name 
     * => return result fetchOne($sql)
     */
    public function querySelectOneByParameter($objParameter){
        $objParameter->setQueryType('SelectOne'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlSelectByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->fetchOne($sql,$sqlvalues);
        return $ret;
    }
    /**
     *\brief Description
     * This method will get DELETE SQL statement By $objParameter
     * EX : execute $sql = DELETE FROM WIDGET WHERE widget_name=:widget_name 
     * => return result query($sql)
     */
    public function queryDeleteByParameter($objParameter){
        $objParameter->setQueryType('Delete'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlDeleteByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->query($sql,$sqlvalues);
        return $ret;
    }
    /**
     *\brief Description
     * This method will get UPDATE SQL statement By $objParameter
     * EX : execute $sql = UPDATE WIDGET SET widget_status=':widget_status' WHERE widget_name=:widget_name 
     * => return result query($sql)
     */
    public function queryUpdateByParameter($objParameter){
        $objParameter->setQueryType('Update'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlUpdateByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->query($sql,$sqlvalues);
        return $ret;
    }
	/**
     *\brief Description
     * This method will get INSERT SQL statement By $objParameter
     * EX : execute $sql = UPDATE WIDGET SET widget_status=':widget_status' WHERE widget_name=:widget_name 
     * => return result query($sql)
     */
    public function queryInsertByParameter($objParameter){
        $objParameter->setQueryType('Insert'); //for queryInfoByParameter($objParameter) used
        $objSql = $this->objFramework->getWidgetPoolFactory()->getSql();
        $sql = $objSql->getSqlInsertByParameter($objParameter);
        $sqlvalues = $objSql->getAssocDataPDO();
        $ret = $this->query($sql,$sqlvalues);
        return $ret;
    }
}